from datetime import datetime, timezone
from typing import Any, Dict, List

import pymysql
from python_depend.mysql_pool import MySQLPool
from python_depend.tidb_data_query import fetch_all_stock_list
from python_depend.time_urtils import get_current_date
from python_depend.workflow_api import work_flow_call


# ==============================
# 数据写入函数
# ==============================
def write_to_mysql(market, symbol, data):
    mysql_pool = MySQLPool.get_pool('feature_quote_data')
    if not data:
        print("没有数据需要保存")
        return
    # 构造单条记录参数（如果后续需要批量插入，可改用 list 并使用 executemany）
    data_date = get_current_date()
    dt = datetime.strptime(data_date, "%Y-%m-%d")
    # 转为UTC时间戳（秒），再乘以1000得到毫秒
    timestamp_ms = int(dt.replace(tzinfo=timezone.utc).timestamp() * 1000)
    market_code = str(market) + ":" + symbol
    params = (
        market_code,
        symbol,
        market,
        data_date,
        timestamp_ms,
        str(data.get('output_tech', "")),
        str(data.get('output_fund', "")),
        str(data.get('output_flow', "")),
        str(data.get('add_dict', ""))
    )

    insert_sql = """
                 INSERT INTO stock_diagnose_detail (`key`, code, market, record_date, record_time, output_tech, \
                                                    output_fund, output_flow, add_dict, create_time, update_time) \
                 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, now(), now()) ON DUPLICATE KEY \
                 UPDATE
                     output_tech = \
                 VALUES (output_tech), output_fund = \
                 VALUES (output_fund), output_flow = \
                 VALUES (output_flow), add_dict = \
                 VALUES (add_dict), update_time = \
                 VALUES (update_time) \
                 """
    insert_history_sql = """
                 INSERT INTO stock_diagnose_detail_history (`key`, code, market, record_date, record_time, output_tech, \
                                                    output_fund, output_flow, add_dict, create_time, update_time) \
                 VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, now(), now()) ON DUPLICATE KEY \
                 UPDATE
                     output_tech = \
                 VALUES (output_tech), output_fund = \
                 VALUES (output_fund), output_flow = \
                 VALUES (output_flow), add_dict = \
                 VALUES (add_dict), update_time = \
                 VALUES (update_time) \
                 """
    delete_sql = " delete from  stock_diagnose_detail where `key` = '" + market_code + "' "

    try:
        connection = mysql_pool.connection()
        with connection.cursor() as cursor:
            # 这里使用 execute 传入单条记录参数；如果需要批量写入改用 executemany(insert_sql, list_of_tuples)
            cursor.execute(delete_sql)
            cursor.execute(insert_sql, params)
            # 写入历史表
            cursor.execute(insert_history_sql, params)
            connection.commit()
    except Exception as e:
        print(f"写入失败: {e}")


def executor_data(market, code, error_code_list):
    input_variable_values = {
        "query": code
    }
    pipe_name = "1263"
    data = work_flow_call(pipe_name, input_variable_values)
    if data is not None and data.get('response') is not None:
        radar_data = data.get('response', {})
        write_to_mysql(market, code, radar_data)
        print(f"{code}执行成功")
    else:
        print(f"数据异常，记录执行异常代码{code}")
        error_code_list.append(code)


def fetch_stock_by_define() -> List[Dict[str, Any]]:
    mysql_pool = MySQLPool.get_pool('ainvestdb')
    """从数据库获取股票列表"""
    sql_query = """
                SELECT CAST(thsmarket_code_hq + 256 AS SIGNED) AS market, \
                       thscode_hq                              AS code
                FROM pub205
                WHERE isvalid = 1
                  AND F005V_PUB205 IN ('212049', '212050', '212010', '212011', '212210')
                  AND f018v_pub205 = '正常上市'
                  AND F007D_PUB205 IS NOT NULL
                  AND thscode_hq IS NOT NULL
                  AND F003V_PUB205 IN ('海外普通股', '优先股', '存托凭证(普通股)', '存托凭证(优先股)', 'UNIT')
                  AND thscode not IN
                      ("A.N", "AAPL.O", "ABBV.N", "ABEV.N", "ABNB.O", "ABT.N", "ACGL.O", "ACN.N", "ADBE.O", "ADI.O", \
                       "ADP.O", "ADSK.O", "AEM.N", "AEP.O", "AFL.N", "AIG.N", "AJG.N", "ALC.N", "ALL.N", "ALNY.O", \
                       "AMAT.O", "AMD.O", "AME.N", "AMGN.O", "AMP.N", "AMT.N", "AMX.N", "AMZN.O", "ANET.N", "AON.N", \
                       "APD.N", "APH.N", "APO.N", "APP.O", "ARES.N", "ARGX.O", "ARM.O", "ASML.O", "AU.N", "AVGO.O", \
                       "AXON.O", "AXP.N", "AZN.O", "AZO.N", "B.N", "BA.N", "BABA.N", "BAC.N", "BAM.N", "BBVA.N", \
                       "BCS.N", "BDX.N", "BHP.N", "BIDU.O", "BK.N", "BKNG.O", "BKR.O", "BLK.N", "BMO.N", "BMY.N", \
                       "BN.N", "BNS.N", "BP.N", "BRK.A.N", "BRK.B.N", "BSX.N", "BTI.N", "BUD.N", "BX.N", "C.N", "CAH.N", \
                       "CARR.N", "CAT.N", "CB.N", "CBRE.N", "CCEP.O", "CCI.N", "CCJ.N", "CCL.N", "CDNS.O", "CEG.O", \
                       "CHT.N", "CHTR.O", "CI.N", "CL.N", "CLS.N", "CM.N", "CMCSA.O", "CME.O", "CMG.N", "CMI.N", \
                       "CNI.N", "CNQ.N", "COF.N", "COIN.O", "COP.N", "COR.N", "COST.O", "CP.N", "CPNG.N", "CPRT.O", \
                       "CRCL.N", "CRH.N", "CRM.N", "CRWD.O", "CRWV.O", "CSCO.O", "CSGP.O", "CSX.O", "CTAS.O", "CTSH.O", \
                       "CTVA.N", "CVE.N", "CVNA.N", "CVS.N", "CVX.N", "D.N", "DAL.N", "DASH.O", "DB.N", "DD.N", \
                       "DDOG.O", "DE.N", "DELL.N", "DEO.N", "DHI.N", "DHR.N", "DIS.N", "DLR.N", "DUK.N", "E.N", "EA.O", \
                       "EBAY.O", "ECL.N", "ED.N", "EL.N", "ELV.N", "EME.N", "EMR.N", "ENB.N", "EOG.N", "EPD.N", \
                       "EQIX.O", "EQNR.N", "EQT.N", "ERIC.O", "ET.N", "ETN.N", "ETR.N", "EW.N", "EXC.O", "EXR.N", "F.N", \
                       "FANG.O", "FAST.O", "FCX.N", "FDX.N", "FER.O", "FERG.N", "FI.N", "FICO.N", "FIS.N", "FIX.N", \
                       "FLUT.N", "FMX.N", "FNV.N", "FTNT.O", "FWONK.O", "GD.N", "GE.N", "GEHC.O", "GEV.N", "GFI.N", \
                       "GILD.O", "GLW.N", "GM.N", "GOOG.O", "GOOGL.O", "GRMN.N", "GS.N", "GSK.N", "GWW.N", "HCA.N", \
                       "HD.N", "HDB.N", "HEI.A.N", "HEI.N", "HIG.N", "HLN.N", "HLT.N", "HMC.N", "HON.O", "HOOD.O", \
                       "HPE.N", "HSBC.N", "HSY.N", "HUM.N", "HWM.N", "IBKR.O", "IBM.N", "IBN.N", "ICE.N", "IDXX.O", \
                       "IMO.A", "INFY.N", "ING.N", "INSM.O", "INTC.O", "INTU.O", "IQV.N", "IR.N", "IRM.N", "ISRG.O", \
                       "ITUB.N", "ITW.N", "JCI.N", "JD.O", "JNJ.N", "JPM.N", "KDP.O", "KHC.O", "KKR.N", "KLAC.O", \
                       "KMB.O", "KMI.N", "KO.N", "KOF.N", "KR.N", "LEN.B.N", "LEN.N", "LHX.N", "LIN.O", "LLY.N", \
                       "LLYVA.O", "LLYVK.O", "LMT.N", "LNG.N", "LOW.N", "LRCX.O", "LVS.N", "LYG.N", "LYV.N", "MA.N", \
                       "MAR.O", "MCD.N", "MCHP.O", "MCK.N", "MCO.N", "MDLZ.O", "MDT.N", "MELI.O", "MET.N", "META.O", \
                       "MFC.N", "MFG.N", "MLM.N", "MMC.N", "MMM.N", "MNST.O", "MO.N", "MPC.N", "MPLX.N", "MPWR.O", \
                       "MRK.N", "MRVL.O", "MS.N", "MSCI.N", "MSFT.O", "MSI.N", "MSTR.O", "MT.N", "MU.O", "MUFG.N", \
                       "NBIS.O", "NDAQ.O", "NEE.N", "NEM.N", "NET.N", "NFLX.O", "NGG.N", "NKE.N", "NOC.N", "NOK.N", \
                       "NOW.N", "NRG.N", "NSC.N", "NTES.O", "NU.N", "NUE.N", "NVDA.O", "NVO.N", "NVS.N", "NWG.N", \
                       "NXPI.O", "O.N", "OKE.N", "ONC.O", "ORCL.N", "ORLY.O", "OTIS.N", "OXY.N", "PANW.O", "PAYX.O", \
                       "PBR.N", "PCAR.O", "PCG.N", "PDD.O", "PEG.N", "PEP.O", "PFE.N", "PG.N", "PGR.N", "PH.N", "PLD.N", \
                       "PLTR.O", "PM.N", "PNC.N", "PRU.N", "PSA.N", "PSTG.N", "PSX.N", "PUK.N", "PWR.N", "PYPL.O", \
                       "QCOM.O", "RACE.N", "RBLX.N", "RCL.N", "RDDT.N", "REGN.O", "RELX.N", "RIO.N", "RJF.N", "RKLB.O", \
                       "RKT.N", "RMD.N", "ROK.N", "ROP.O", "ROST.O", "RSG.N", "RTX.N", "RY.N", "RYAAY.O", "SAN.N", \
                       "SAP.N", "SBUX.O", "SCCO.N", "SCHW.N", "SE.N", "SHEL.N", "SHOP.O", "SHW.N", "SLB.N", "SLF.N", \
                       "SMCI.O", "SMFG.N", "SNOW.N", "SNPS.O", "SNY.O", "SO.N", "SOFI.O", "SONY.N", "SPG.N", "SPGI.N", \
                       "SPOT.N", "SRE.N", "STLA.N", "STT.N", "STX.O", "SU.N", "SYK.N", "SYM.O", "SYY.N", "T.N", "TAK.N", \
                       "TCOM.O", "TD.N", "TDG.N", "TEAM.O", "TEL.N", "TFC.N", "TGT.N", "TJX.N", "TKO.N", "TM.N", \
                       "TME.N", "TMO.N", "TMUS.O", "TRGP.N", "TRI.O", "TRP.N", "TRV.N", "TSLA.O", "TSM.N", "TT.N", \
                       "TTE.N", "TTWO.O", "TXN.O", "UAL.O", "UBER.N", "UBS.N", "UI.N", "UL.N", "UNH.N", "UNP.N", \
                       "UPS.N", "URI.N", "USB.N", "V.N", "VALE.N", "VEEV.N", "VICI.N", "VLO.N", "VMC.N", "VRSK.O", \
                       "VRT.N", "VRTX.O", "VST.N", "VTR.N", "VZ.N", "WAB.N", "WBD.O", "WCN.N", "WDAY.O", "WDC.O", \
                       "WDS.N", "WEC.N", "WELL.N", "WFC.N", "WM.N", "WMB.N", "WMT.N", "WPM.N", "WTW.O", "XEL.O", \
                       "XOM.N", "XYL.N", "XYZ.N", "YUM.N", "ZS.O", "ZTS.N") \
                """
    try:
        with mysql_pool.connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql_query)
                return cursor.fetchall()
    except pymysql.Error as e:
        print(f"[ERROR] 获取股票列表失败: {e}")
        return []


# ==============================
# 主执行函数
# ==============================
def execute_job():
    """主任务执行流程"""
    error_code_list = []
    print("开始执行任务")
    if not stock_list:
        print("股票列表为空，任务终止")
        return
    print("查询股票数量：" + str(len(stock_list)))
    # 2. 处理每只股票
    count = 0
    for stock in stock_list:
        count = count + 1
        try:
            code = stock['code']
            market = stock['market']
            print(f"处理第{count}只股票: Code={code},")
            executor_data(market, code, error_code_list)
        except Exception as e:
            print("执行异常", e)
    if len(error_code_list) <= 100:
        print(f"任务执行完成,执行失败代码:{str(error_code_list)}")
    else:
        print(f"任务执行完成,执行失败代码:{str(error_code_list)}")
        # 存在异常代码，退出告警
        exit(1)


# ==============================
# 程序入口
# ==============================
if __name__ == "__main__":
    # # 获取所有股票
    # stock_list = [{"code": "AAPL", "market": 185}]
    MySQLPool.initialize()
    stock_list = fetch_stock_by_define()
    execute_job()
    MySQLPool.close_all()
